package database;

import java.sql.*;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import auxilary.Configurations;
import auxilary.Log;

import exceptions.DBException;
import exceptions.MyException;

/* ******************************************************** */
/* 							DBHandler						*/
/*															*/
/*		Takes care of communicating with the database		*/
/*															*/
/* ******************************************************** */

public class DBHandler {
	// public final static params
	public static final int COL_NAME = 0;
	public static final int COL_VAL = 1;
	public static final String SORT_ASC = "ASC";
	public static final String SORT_DESC = "DESC";

	// private params
	private static String url = null;
	private static String password = null;
	private static String username = null;

	// constructor
	public DBHandler() {
		url = Configurations.DB.URL;
		password = Configurations.DB.PASSWORD;
		username = Configurations.DB.USERNAME;
	}

	/* ********************* */
	/* Connection management */
	/* ********************* */

	public Connection getConnection() throws DBException {
		try {
			// Obtain our environment naming context
			Context c = (Context) new InitialContext().lookup("java:comp/env");
			DataSource ds = (DataSource) c.lookup("jdbc/DBPool");
			return ds.getConnection();
		} catch (Exception e) {
			try {
				Class.forName("com.mysql.jdbc.Driver").newInstance();
				if (url == null || password == null || username == null) {
					throw new MyException(
							"no DBPool, no configuration file, no connection");
				}
				Connection c = DriverManager.getConnection(url, username,
						password);
				return c;
			} catch (Exception e2) {
				Log.err("DB config trouble: " + e.toString());
				throw new DBException(
						"no DBPool, no configuration file, no connection");
			}
		}

	}

	public void closeConnection(Connection c) {
		try {
			if (c != null)
				c.close();
		} catch (Exception ignore) {
		}
	}

	public void closeConnection(ResultSet rs) {
		Statement s = null;
		Connection c = null;
		try {
			if (rs != null) {
				s = rs.getStatement();
				rs.close();
			}
			if (s != null) {
				c = s.getConnection();
				s.close();
			}
			if (c != null)
				c.close();
		} catch (Exception e) {
			Log.err("Connection closing failed");
		}
	}

	/* ****************** */
	/* Insert into the DB */
	/* ****************** */

	/*
	 * Insert - insert data array into the database
	 */
	public int insert(Object[][] data, String tableName, Connection conn) throws DBException, SQLException {
		boolean gotConnection = (conn != null);
		// indicator of the first iteration
		boolean f = true;
		// create the column names part of the statement
		String columns = "(";
		for (Object[] obj : data) {
			if (!f && obj[COL_NAME] != null && obj[COL_VAL] != null)
				columns = columns.concat(",");
			if (obj[COL_NAME] != null && obj[COL_VAL] != null)
				columns = columns.concat(obj[COL_NAME].toString());
			f = false;
		}
		columns = columns.concat(")");
		f = true;
		// create the values part of the statement
		String values = " VALUES (";
		for (Object[] obj : data) {
			if (!f && obj[COL_NAME] != null && obj[COL_VAL] != null)
				values = values.concat(",");
			if (obj[COL_NAME] != null && obj[COL_VAL] != null)
				values = values.concat("\"" + obj[COL_VAL].toString() + "\"");
			f = false;
		}
		values = values.concat(")");
		// execute query
		String query = "INSERT INTO " + tableName + " " + columns + " "
				+ values + ";";
		int res = 0;
		try {
			if (!gotConnection) {
				conn = this.getConnection();
			}
			Statement stmnt = conn.createStatement();
			stmnt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
			// get pk
			ResultSet rs = stmnt.getGeneratedKeys();
			if (rs.next())
				res = rs.getInt(1);
			rs.close();
		} catch (SQLException e) {
			throw e;
		} catch (MyException e) {
			Log.err("didn't get connection");
		} finally {
			if (!gotConnection) {
				closeConnection(conn);
			}
		}
		return res;
	}

	/*
	 * Insert - insert data in to the database
	 */
	public int insert(String tableName, String data) throws DBException {

		String query = "INSERT INTO " + tableName + " " + data + ";";
		int res = 0;
		Connection con = null;
		try {
			con = this.getConnection();
			Statement sttmnt = con.createStatement();
			sttmnt.executeUpdate(query);
			// get pk
			ResultSet rs = con.createStatement().getGeneratedKeys();
			if (rs.next())
				res = rs.getInt(1);
		} catch (SQLException e) {
			// insert failed
			throw new DBException(tableName, query, e.getMessage(),
					DBException.DEFAULT);
		} catch (MyException e) {
			Log.err("get connection failed");
		} finally {
			closeConnection(con);
		}
		return res;
	}

	/* ***************** */
	/* Fetch from the DB */
	/* ***************** */

	/*
	 * Fetch - get row from the DB (condition: where) returns ResultSet
	 */
	public ResultSet fetch(String tableName, String where, Connection conn) throws DBException, SQLException {
		return fetch(tableName, where, -1, -1, null, null, conn);
	}

	/*
	 * Fetch - get row from the DB (condition: where, order, sortBy) returns
	 * ResultSet
	 */
	public ResultSet fetch(String tableName, String where, int fromRow,
			int toRow, String sortBy, String order, Connection conn) throws DBException, SQLException {
		boolean gotConnection = (conn != null);
		// create filters
		String filters = "", query = null;
		ResultSet res = null;
		if (where != null)
			filters += " WHERE " + where + " ";
		if (sortBy != null) {
			filters += " ORDER BY " + sortBy + " ";
			if (order != null)
				filters += order + " ";
		}
		// add row limit to filters
		if (fromRow >= 0 && toRow >= 0) {
			if (toRow < fromRow)
				throw new DBException(tableName, null, new String(
						"invalid row index values"), DBException.BAD_PARAMS);
			filters += " LIMIT " + fromRow + "," + (toRow - fromRow + 1) + " ";
		}

		// create query
		query = "SELECT * FROM " + tableName + filters + ";";
		// execute query
		try {
			// create updatable ResultSets
			if (!gotConnection) {
				conn = getConnection();
			}
			Statement stmt = conn
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);
			res = stmt.executeQuery(query);
		} catch (SQLException e) {
			throw e;
		} catch (MyException e) {
			Log.err("Failed to establish connection");
		}
		return res;

	}

	
	/* ******* */
	/* Helpers */
	/* ******* */

	/*
	 * Is Empty ResultSet - check if the Resultset is empty
	 */
	public boolean isEmptyResultSet(ResultSet rs, String tableName)
			throws DBException {
		try {
			if (!rs.first()) {
				rs.beforeFirst();
				return true;
			}
			rs.beforeFirst();
		} catch (SQLException e) {
			throw new DBException(tableName, "IS_EMPTY", e.getMessage(),
					DBException.DEFAULT);
		}
		return false;
	}

	/*
	 * Get ResultSet Size - return the number of rows in the ResultSet
	 */
	protected int getResultSetSize(ResultSet rs) throws DBException {
		// get the number of rows
		int rowCount = 0;
		try {
			rs.last();
			rowCount = rs.getRow();
			rs.beforeFirst();
		} catch (SQLException e) {
			throw new DBException(null, "ON RESULT SET ROW COUNT",
					e.getMessage(), DBException.DEFAULT);
		}
		return rowCount;
	}

	/*
	 * Count - count rows of results (condition: where)
	 */
	protected int count(String table, String where) throws DBException {
		int n = 0;
		String query = "SELECT COUNT(*) FROM " + table + " WHERE " + where
				+ ";";
		Connection con = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			Statement stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			if (rs.next())
				n = rs.getInt(1);
		} catch (SQLException e) {
			throw new DBException(new String(table), new String(query),
					e.getMessage(), DBException.DEFAULT);
		} catch (MyException e) {
			Log.err("connection couldn't be established");
		} finally {
			closeConnection(rs);
			closeConnection(con);
		}
		return n;
	}

}
